package com.example.mapper;

import com.example.pojo.Videos;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface VideosMapper {
    @Select("SELECT i.id, i.title,i.description,i.url,i.region_id,i.species_id,p.region_name regionName,s.species_name speciesName " +
            "FROM videos i " +
            "LEFT JOIN heritage_category_region p ON i.region_id = p.id " +
            "LEFT JOIN heritage_category_species s ON i.species_id = s.id") // 使用 JOIN 查询
    List<Videos> findAll();

    // 根据 id 查询
    @Select("SELECT i.id, i.title,i.description,i.url,i.region_id,i.species_id,p.region_name regionName,s.species_name speciesName " +
            "FROM videos i " +
            "LEFT JOIN heritage_category_region p ON i.region_id = p.id " +
            "LEFT JOIN heritage_category_species s ON i.species_id = s.id " +
            "WHERE i.id = #{id}")
    Videos findById(Integer id);

    // 删除视频
    @Delete("DELETE FROM videos WHERE id = #{id}")
    void delete(Integer id);

    // 增加视频
    @Insert("INSERT INTO videos (title, description, url, region_id, species_id) VALUES (#{title}, #{description}, #{url}, #{regionId}, #{speciesId})")
    void insert(Videos video);

    // 根据地区查询id
    @Select("SELECT id FROM heritage_category_region WHERE region_name = #{regionName}")
    Integer findRegionIdByName(String regionName);
    // 根据物种查询id
    @Select("SELECT id FROM heritage_category_species WHERE species_name = #{speciesName}")
    Integer findSpeciesIdByName(String speciesName);

    //更新视频
    @Update("UPDATE videos SET title = #{title}, description = #{description}, url = #{url}, region_id = #{regionId}, species_id = #{speciesId} WHERE id = #{id}")
    void update(Videos video);
}
